﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Models.DataModel;
using ContributionDeposit.CoreFrontEnd;
using System.IO;
namespace ContributionDeposit.Controllers
{
    
    public class ImportController : Core_FrontEnd
    {
        //
        // GET: /Import/
        public ActionResult ImportCompany()
        {
            CheckUserLogin = CheckLogin();
            if (CheckUserLogin == true)
                return View();
            else
                return RedirectToAction("Login", "Account", new { ReturnUrl = Request.Url.AbsoluteUri });
        }
        public ActionResult ImportPort()
        {
            CheckUserLogin = CheckLogin();
            if (CheckUserLogin == true)
                return View();
            else
                return RedirectToAction("Login", "Account", new { ReturnUrl = Request.Url.AbsoluteUri });
        }
        [HttpPost]
        public ActionResult ImportExcelFileToDatabaseCompany(HttpPostedFileBase file)
        {
            try
            {
                if (Request.Files["file"].ContentLength > 0)
                {
                    string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
                    if (fileExtension == ".xls" || fileExtension == ".xlsx")
                    {
                        string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/App_Data/ExcelFiles"), Request.Files["file"].FileName);
                        if (System.IO.File.Exists(fileLocation))
                            System.IO.File.Delete(fileLocation);
                        Request.Files["file"].SaveAs(fileLocation);// Lưu file vào MapPath
                        string excelConnectionString = string.Empty;
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        if (fileExtension == ".xls")
                        {
                            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        }
                        else if (fileExtension == ".xlsx")
                        {

                            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        }
                        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                        excelConnection.Open();
                        DataTable dt = new DataTable();

                        dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        if (dt == null)
                        {
                            return null;
                        }
                        String[] excelSheets = new String[dt.Rows.Count];
                        int t = 0;
                        foreach (DataRow row in dt.Rows)
                        {
                            excelSheets[t] = row["TABLE_NAME"].ToString();
                            t++;
                        }
                        OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
                        DataSet ds = new DataSet();

                        string query = string.Format("Select * from [{0}]", excelSheets[0]);
                        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                        {
                            dataAdapter.Fill(ds);
                        }
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            UserAccount acc = fun_db.GetObjUserByUsername(Session["user"].ToString());
                            Country country = fun_db.GetObjCountryByName(ds.Tables[0].Rows[i]["CountryName"].ToString());
                            Company model = new Company();
                            model.CompanyId = Guid.NewGuid();
                            model.CompanyNumber = ds.Tables[0].Rows[i]["CompanyNumber"].ToString();
                            model.Name = ds.Tables[0].Rows[i]["Name"].ToString();
                            model.Address1 = ds.Tables[0].Rows[i]["Address1"].ToString();
                            model.Address2 = ds.Tables[0].Rows[i]["Address2"].ToString();
                            model.City = ds.Tables[0].Rows[i]["City"].ToString();
                            model.ZipCode = ds.Tables[0].Rows[i]["ZipCode"].ToString();
                            model.StateOrProvice = ds.Tables[0].Rows[i]["StateOrProvince"].ToString();
                            model.CountryId = country.CountryId;
                            model.CreatedOn = DateTime.Now;
                            model.CreatedBy = acc.UserAccountID;
                            model.ModifiedOn = DateTime.Now;
                            model.ModifiedBy = acc.UserAccountID;
                            model.ModifiedByName = acc.UserName;
                            CONDEPOSITEntities db = new CONDEPOSITEntities();
                            db.Companies.Add(model);
                            db.SaveChanges();
                        }
                        ViewBag.message = "Information saved successfully.";
                    }
                    else
                    {
                        ViewBag.message = "Save data fail. Please try again.";
                    }
                }
                else
                {
                    ModelState.AddModelError("", "Plese select Excel File.");
                }
            }
            catch
            {

            }
            return RedirectToAction("ImportCompany");
        }
        public ActionResult DownloadFilePort()
        {
            string filename = "Port.xlsx";
            string filepath = AppDomain.CurrentDomain.BaseDirectory + "App_Data/ExcelFiles/TemplatePort.xlsx";
            byte[] filedata = System.IO.File.ReadAllBytes(filepath);
            string contentType = MimeMapping.GetMimeMapping(filepath);

            var cd = new System.Net.Mime.ContentDisposition
            {
                FileName = filename,
                Inline = true,
            };

            Response.AppendHeader("Content-Disposition", cd.ToString());

            return File(filedata, contentType);
        }
        public ActionResult DownloadFileCompany()
        {
            string filename = "Company.xlsx";
            string filepath = AppDomain.CurrentDomain.BaseDirectory + "App_Data/ExcelFiles/TemplateCompany.xlsx";
            byte[] filedata = System.IO.File.ReadAllBytes(filepath);
            string contentType = MimeMapping.GetMimeMapping(filepath);

            var cd = new System.Net.Mime.ContentDisposition
            {
                FileName = filename,
                Inline = true,
            };

            Response.AppendHeader("Content-Disposition", cd.ToString());

            return File(filedata, contentType);
        }
        [HttpPost]
        public ActionResult ImportExcelFileToDatabasePort(HttpPostedFileBase file)
        {
            try
            {
                if (Request.Files["file"].ContentLength > 0)
                {
                    string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
                    if (fileExtension == ".xls" || fileExtension == ".xlsx")
                    {
                        string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/App_Data/ExcelFiles"), Request.Files["file"].FileName);
                        if (System.IO.File.Exists(fileLocation))
                            System.IO.File.Delete(fileLocation);
                        Request.Files["file"].SaveAs(fileLocation);// Lưu file vào MapPath
                        string excelConnectionString = string.Empty;
                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        if (fileExtension == ".xls")
                        {
                            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                        }
                        else if (fileExtension == ".xlsx")
                        {

                            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                        }
                        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                        excelConnection.Open();
                        DataTable dt = new DataTable();

                        dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        if (dt == null)
                        {
                            return null;
                        }
                        String[] excelSheets = new String[dt.Rows.Count];
                        int t = 0;
                        foreach (DataRow row in dt.Rows)
                        {
                            excelSheets[t] = row["TABLE_NAME"].ToString();
                            t++;
                        }
                        OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
                        DataSet ds = new DataSet();

                        string query = string.Format("Select * from [{0}]", excelSheets[0]);
                        using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                        {
                            dataAdapter.Fill(ds);
                        }
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            UserAccount acc = fun_db.GetObjUserByUsername(Session["user"].ToString());
                            Country country = fun_db.GetObjCountryByName(ds.Tables[0].Rows[i]["CountryName"].ToString());
                            Port model = new Port();
                            model.PortId = Guid.NewGuid();
                            model.PortNumber = ds.Tables[0].Rows[i]["PortNumber"].ToString();
                            model.Name = ds.Tables[0].Rows[i]["Name"].ToString();
                            model.Address1 = ds.Tables[0].Rows[i]["Address1"].ToString();
                            model.Address2 = ds.Tables[0].Rows[i]["Address2"].ToString();
                            model.City = ds.Tables[0].Rows[i]["City"].ToString();
                            model.ZipCode = ds.Tables[0].Rows[i]["ZipCode"].ToString();
                            model.StateOrProvice = ds.Tables[0].Rows[i]["StateOrProvince"].ToString();
                            model.CountryId = country.CountryId;
                            model.CreatedOn = DateTime.Now;
                            model.CreatedBy = acc.UserAccountID;
                            model.ModifiedOn = DateTime.Now;
                            model.ModifiedBy = acc.UserAccountID;
                            model.ModifiedByName = acc.UserName;
                            CONDEPOSITEntities db = new CONDEPOSITEntities();
                            db.Ports.Add(model);
                            db.SaveChanges();
                        }
                        ViewBag.message = "Information saved successfully.";
                        return RedirectToAction("Port", "Port");
                    }
                    else
                    {
                        ViewBag.message = "Save data fail. Please try again.";
                    }
                }
                else
                {
                    ModelState.AddModelError("", "Plese select Excel File.");
                }
            }
            catch
            {

            }
            return RedirectToAction("ImportPort");
        }
	}
}